#include "dbsqliteinterface.h"

DBSqliteInterface::DBSqliteInterface(const QString& databasePath, QObject* parent) :m_databasePath{databasePath}, DBInterface{parent}
{
}

DBSqliteInterface::~DBSqliteInterface()
{
    closeDatabase();
}

// 数据库连接管理
bool DBSqliteInterface::openDatabase(const QString& connectionName)
{
    if(m_database.isOpen()) {
        return true; // 数据库已打开
    }
    m_connectionName = connectionName.isEmpty() ? QSqlDatabase::defaultConnection : connectionName;

    m_database = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);
    logger()->info() << "m_databasePath:" << m_databasePath;
    m_database.setDatabaseName(m_databasePath);
    if (!m_database.open()) {
        logger()->error() << "Failed to open database:" << m_database.lastError().text();
        return false; // 打开数据库失败
    }

    executeQuery("PRAGMA foreign_keys = ON;"); // 启用外键约束
    return true;
}

// 关闭数据库
void DBSqliteInterface::closeDatabase()
{
    if(m_database.isOpen()) {
        m_database.close();
    }
    if(m_connectionName != QSqlDatabase::defaultConnection) {
        QSqlDatabase::removeDatabase(m_connectionName);
    }
}

bool DBSqliteInterface::isDatabaseOpen() const
{
    return m_database.isOpen();
}

// 数据库初始化
bool DBSqliteInterface::initializeDatabase() {
    if(!isDatabaseOpen() && !openDatabase()) {
        return false;
    }

    return createTables();
};

bool DBSqliteInterface::createTables() {
    QString createMowerInfoTableQuery = R"(
        CREATE TABLE IF NOT EXISTS MowerInfo (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sn TEXT UNIQUE NOT NULL,
            wifi_mac TEXT UNIQUE NOT NULL,
            ip TEXT,
            bt_mac TEXT,
            create_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            update_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
    )";
    if(!executeQuery(createMowerInfoTableQuery)) {
        logger()->error() << "Failed to create MowerInfo table:" << lastError();
        return false;
    }

    QString createSNIndexQuery = R"(
        CREATE INDEX IF NOT EXISTS idx_mowerinfo_sn ON MowerInfo(sn);
    )";
    if(!executeQuery(createSNIndexQuery)) {
        logger()->error() << "Failed to create index on sn:" << lastError();
        return false;
    }
    QString createMacIndexQuery = R"(
        CREATE INDEX IF NOT EXISTS idx_mowerinfo_wifi_mac ON MowerInfo(wifi_mac);
    )";
    if(!executeQuery(createMacIndexQuery)) {
        logger()->error() << "Failed to create index on wifi_mac:" << lastError();
        return false;
    }

    QString createTriggerQuery = R"(
        CREATE TRIGGER IF NOT EXISTS update_mowerinfo_at
        AFTER UPDATE ON MowerInfo
        FOR EACH ROW
        BEGIN
            UPDATE MowerInfo SET update_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END;
    )";
    if(!executeQuery(createTriggerQuery)) {
        logger()->error() << "Failed to create trigger:" << lastError();
        return false;
    }

    return true;
};

// 基本CRUD操作
bool DBSqliteInterface::insertMowerInfo(const MowerInfoBase& mowerInfo)
{
    QString insertQuery = R"(
        INSERT INTO MowerInfo (sn, wifi_mac, ip, bt_mac)
        VALUES (:sn, :wifi_mac, :ip, :bt_mac);
    )";
    QMap<QString, QVariant> params;
    params["sn"] = mowerInfo.sn;
    params["wifi_mac"] = mowerInfo.wifi_mac;
    params["ip"] = mowerInfo.ip;
    params["bt_mac"] = mowerInfo.bt_mac;
    return executeQuery(insertQuery, params);
}

bool DBSqliteInterface::updateMowerInfo(qint32 id, const MowerInfoBase& mowerInfo)
{
    QString updateQuery = R"(
        UPDATE MowerInfo
        SET sn = :sn, wifi_mac = :wifi_mac, ip = :ip, bt_mac = :bt_mac
        WHERE id = :id;
    )";
    QMap<QString, QVariant> params;
    params["id"] = id;
    params["sn"] = mowerInfo.sn;
    params["wifi_mac"] = mowerInfo.wifi_mac;
    params["ip"] = mowerInfo.ip;
    params["bt_mac"] = mowerInfo.bt_mac;
    return executeQuery(updateQuery, params);
}

bool DBSqliteInterface::deleteMowerInfo(qint32 id)
{
    QString deleteQuery = "DELETE FROM MowerInfo WHERE id = :id;";
    QMap<QString, QVariant> params;
    params["id"] = id;
    return executeQuery(deleteQuery, params);
}

QVector<MowerInfoInDb> DBSqliteInterface::selectMowerInfos()
{
    QString selectQuery = "SELECT * FROM MowerInfo;";
    QSqlQuery sqlQuery(m_database);
    if(!sqlQuery.exec(selectQuery)) {
        logger()->error() << "Failed to select MowerInfo:" << sqlQuery.lastError().text();
        return QVector<MowerInfoInDb>();
    }

    QVector<MowerInfoInDb> mowerInfos;
    while(sqlQuery.next()) {
        MowerInfoInDb info;
        info.id = sqlQuery.value("id").toInt();
        info.sn = sqlQuery.value("sn").toString();
        info.wifi_mac = sqlQuery.value("wifi_mac").toString();
        info.ip = sqlQuery.value("ip").toString();
        info.bt_mac = sqlQuery.value("bt_mac").toString();
        info.create_at = sqlQuery.value("create_at").toDateTime();
        info.update_at = sqlQuery.value("update_at").toDateTime();
        mowerInfos.append(info);
    }
    return mowerInfos;
}

MowerInfoInDb DBSqliteInterface::selectMowerInfoById(qint32 id)
{
    QString selectQuery = "SELECT * FROM MowerInfo WHERE id = :id;";
    QSqlQuery sqlQuery(m_database);
    sqlQuery.prepare(selectQuery);
    sqlQuery.bindValue(":id", id);

    if(!sqlQuery.exec()) {
        logger()->error() << "Failed to select MowerInfo by id:" << sqlQuery.lastError().text();
        return MowerInfoInDb(); // 返回一个空的MowerInfoInDb对象
    }

    if(sqlQuery.next()) {
        MowerInfoInDb info;
        info.id = sqlQuery.value("id").toInt();
        info.sn = sqlQuery.value("sn").toString();
        info.wifi_mac = sqlQuery.value("wifi_mac").toString();
        info.ip = sqlQuery.value("ip").toString();
        info.bt_mac = sqlQuery.value("bt_mac").toString();
        info.create_at = sqlQuery.value("create_at").toDateTime();
        info.update_at = sqlQuery.value("update_at").toDateTime();
        return info;
    }
    return MowerInfoInDb(); // 未找到返回空对象
}

std::optional<MowerInfoInDb> DBSqliteInterface::selectMowerInfosBySn(const QString& sn)
{
    QString selectQuery = "SELECT * FROM MowerInfo WHERE sn = :sn;";
    QSqlQuery sqlQuery(m_database);
    sqlQuery.prepare(selectQuery);
    sqlQuery.bindValue(":sn", sn);

    if(!sqlQuery.exec()) {
        logger()->error() << "Failed to select MowerInfo by sn:" << sqlQuery.lastError().text();
        return std::nullopt; // 查询失败
    }

    if(sqlQuery.next()) {
        MowerInfoInDb info;
        info.id = sqlQuery.value("id").toInt();
        info.sn = sqlQuery.value("sn").toString();
        info.wifi_mac = sqlQuery.value("wifi_mac").toString();
        info.ip = sqlQuery.value("ip").toString();
        info.bt_mac = sqlQuery.value("bt_mac").toString();
        info.create_at = sqlQuery.value("create_at").toDateTime();
        info.update_at = sqlQuery.value("update_at").toDateTime();
        return info; // 返回找到的MowerInfoInDb对象
    }
    return std::nullopt; // 未找到返回空
}

std::optional<MowerInfoInDb> DBSqliteInterface::selectMowerInfosByMac(const QString& wifi_mac)
{
    QString selectQuery = "SELECT * FROM MowerInfo WHERE wifi_mac = :wifi_mac;";
    QSqlQuery sqlQuery(m_database);
    sqlQuery.prepare(selectQuery);
    sqlQuery.bindValue(":wifi_mac", wifi_mac);

    if(!sqlQuery.exec()) {
        logger()->error() << "Failed to select MowerInfo by wifi_mac:" << sqlQuery.lastError().text();
        return std::nullopt; // 查询失败
    }

    if(sqlQuery.next()) {
        MowerInfoInDb info;
        info.id = sqlQuery.value("id").toInt();
        info.sn = sqlQuery.value("sn").toString();
        info.wifi_mac = sqlQuery.value("wifi_mac").toString();
        info.ip = sqlQuery.value("ip").toString();
        info.bt_mac = sqlQuery.value("bt_mac").toString();
        info.create_at = sqlQuery.value("create_at").toDateTime();
        info.update_at = sqlQuery.value("update_at").toDateTime();
        return info; // 返回找到的MowerInfoInDb对象
    }
    return std::nullopt; // 未找到返回空
}

// 事务支持
bool DBSqliteInterface::beginTransaction()
{
    return m_database.transaction();
}

bool DBSqliteInterface::commitTransaction()
{
    return m_database.commit();
}

bool DBSqliteInterface::rollbackTransaction()
{
    return m_database.rollback();
}

// 实用功能
QString DBSqliteInterface::lastError() const
{
    return m_database.lastError().text();
}

bool DBSqliteInterface::executeQuery(
        const QString& query, const QMap<QString, QVariant>& params)
{
    QSqlQuery sqlQuery(m_database);
    sqlQuery.prepare(query);

    // 绑定参数
    for (auto it = params.constBegin(); it != params.constEnd(); ++it) {
        sqlQuery.bindValue(":" + it.key(), it.value());
    }

    if (!sqlQuery.exec()) {
        logger()->error() << "Failed to execute query:" << sqlQuery.lastError().text();
        logger()->error() << "sql:" << query;
        return false;
    }
    return true;
}

bool DBSqliteInterface::tableExists(const QString& tableName)
{
    if(tableName.isEmpty()) {
        return false; // 表名不能为空
    }

    QString query = QString("SELECT name FROM sqlite_master WHERE type='table' AND name='%1';").arg(tableName);
    QSqlQuery sqlQuery(m_database);
    if(!sqlQuery.exec(query)) {
        logger()->error() << "Failed to check table existence:" << sqlQuery.lastError().text();
        return false;
    }

    return sqlQuery.next();
}
